package com.qdhh.enrollment.bootstrap;

import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;

@Slf4j
@Component
public class DbPatcher {
    private final JdbcTemplate jdbc;

    public DbPatcher(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    @PostConstruct
    public void initialize() {
        try {
            // 测试数据库连接
            jdbc.execute("SELECT 1");
            log.info("Database connection successful, starting schema initialization...");
        } catch (Exception e) {
            log.error("========================================");
            log.error("数据库连接失败！");
            log.error("错误信息: {}", e.getMessage());
            log.error("");
            log.error("请检查以下配置:");
            log.error("1. MySQL 服务是否已启动");
            log.error("2. 数据库 'new-student' 是否存在");
            log.error("3. application.yml 中的数据库用户名和密码是否正确");
            log.error("   默认配置: username=root, password=1234");
            log.error("   可通过环境变量覆盖: DB_MASTER_USERNAME, DB_MASTER_PASSWORD");
            log.error("");
            log.error("创建数据库命令:");
            log.error("  CREATE DATABASE `new-student` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;");
            log.error("========================================");
            // 不抛出异常，允许应用继续启动（但数据库功能将不可用）
            return;
        }
        
        try {
            ensureQuizSchema();
            ensureGuideSchema();
            ensureFaqSchema();
            ensureMilestoneSchema();
            seedGuides();
            seedFaqs();
            log.info("Database schema initialization completed successfully.");
        } catch (Exception e) {
            log.error("Database schema initialization failed: {}", e.getMessage(), e);
        }
    }

    private void ensureQuizSchema() {
        patchColumn("quiz_questions", "type",
            "ALTER TABLE quiz_questions ADD COLUMN type VARCHAR(32) NOT NULL DEFAULT 'single'");
        patchColumn("quiz_questions", "enabled",
            "ALTER TABLE quiz_questions ADD COLUMN enabled TINYINT(1) NOT NULL DEFAULT 1");
        patchColumn("quiz_questions", "order_num",
            "ALTER TABLE quiz_questions ADD COLUMN order_num INT NOT NULL DEFAULT 0");
        patchColumn("quiz_questions", "created_at",
            "ALTER TABLE quiz_questions ADD COLUMN created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP");

        execIgnore("CREATE INDEX idx_quiz_questions_enabled ON quiz_questions(enabled)");
        execIgnore("CREATE INDEX idx_quiz_questions_order   ON quiz_questions(order_num)");
    }

    private void ensureGuideSchema() {
        try {
            jdbc.execute("CREATE TABLE IF NOT EXISTS guides (" +
                "id VARCHAR(64) PRIMARY KEY," +
                "title VARCHAR(255) NOT NULL," +
                "type VARCHAR(64) NOT NULL," +
                "content TEXT," +
                "link VARCHAR(512)," +
                "order_num INT NOT NULL DEFAULT 0," +
                "enabled TINYINT(1) NOT NULL DEFAULT 1," +
                "deleted TINYINT NOT NULL DEFAULT 0 COMMENT '软删除'," +
                "version INT NOT NULL DEFAULT 1 COMMENT '乐观锁版本号'," +
                "created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP," +
                "updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
        } catch (Exception e) {
            log.warn("DB patch: failed to ensure guides table exists: {}", e.getMessage());
        }
        // 添加缺失的字段（如果表已存在但字段缺失）
        patchColumn("guides", "deleted",
            "ALTER TABLE guides ADD COLUMN deleted TINYINT NOT NULL DEFAULT 0 COMMENT '软删除'");
        patchColumn("guides", "version",
            "ALTER TABLE guides ADD COLUMN version INT NOT NULL DEFAULT 1 COMMENT '乐观锁版本号'");
        patchColumn("guides", "updated_at",
            "ALTER TABLE guides ADD COLUMN updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
        execIgnore("CREATE INDEX idx_guides_enabled ON guides(enabled)");
        execIgnore("CREATE INDEX idx_guides_type_order ON guides(type, order_num)");
    }

    private void ensureFaqSchema() {
        try {
            jdbc.execute("CREATE TABLE IF NOT EXISTS faqs (" +
                "id VARCHAR(64) PRIMARY KEY," +
                "question VARCHAR(512) NOT NULL," +
                "answer TEXT NOT NULL," +
                "tags VARCHAR(255) NULL," +
                "view_count INT NOT NULL DEFAULT 0," +
                "order_num INT NOT NULL DEFAULT 0," +
                "enabled TINYINT(1) NOT NULL DEFAULT 1," +
                "deleted TINYINT NOT NULL DEFAULT 0 COMMENT '软删除'," +
                "version INT NOT NULL DEFAULT 1 COMMENT '乐观锁版本号'," +
                "created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP," +
                "updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
        } catch (Exception e) {
            log.warn("DB patch: failed to ensure faqs table exists: {}", e.getMessage());
        }
        // 添加缺失的字段（如果表已存在但字段缺失）
        patchColumn("faqs", "view_count",
            "ALTER TABLE faqs ADD COLUMN view_count INT NOT NULL DEFAULT 0");
        patchColumn("faqs", "deleted",
            "ALTER TABLE faqs ADD COLUMN deleted TINYINT NOT NULL DEFAULT 0 COMMENT '软删除'");
        patchColumn("faqs", "version",
            "ALTER TABLE faqs ADD COLUMN version INT NOT NULL DEFAULT 1 COMMENT '乐观锁版本号'");
        patchColumn("faqs", "updated_at",
            "ALTER TABLE faqs ADD COLUMN updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
        execIgnore("CREATE INDEX idx_faqs_enabled ON faqs(enabled)");
        execIgnore("CREATE INDEX idx_faqs_order ON faqs(order_num, created_at)");
        execIgnore("CREATE INDEX idx_faqs_question ON faqs(question(191))");
        execIgnore("CREATE INDEX idx_faqs_tags ON faqs(tags(191))");
    }

    private void ensureMilestoneSchema() {
        try {
            jdbc.execute("CREATE TABLE IF NOT EXISTS milestones (" +
                "id VARCHAR(64) PRIMARY KEY," +
                "title VARCHAR(255) NOT NULL," +
                "description TEXT," +
                "date DATE," +
                "type VARCHAR(64)," +
                "order_num INT NOT NULL DEFAULT 0," +
                "enabled TINYINT(1) NOT NULL DEFAULT 1," +
                "deleted TINYINT NOT NULL DEFAULT 0 COMMENT '软删除'," +
                "version INT NOT NULL DEFAULT 1 COMMENT '乐观锁版本号'," +
                "created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP," +
                "updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
        } catch (Exception e) {
            log.warn("DB patch: failed to ensure milestones table exists: {}", e.getMessage());
        }
        // 添加缺失的字段（如果表已存在但字段缺失）
        patchColumn("milestones", "type",
            "ALTER TABLE milestones ADD COLUMN type VARCHAR(64)");
        patchColumn("milestones", "enabled",
            "ALTER TABLE milestones ADD COLUMN enabled TINYINT(1) NOT NULL DEFAULT 1");
        patchColumn("milestones", "deleted",
            "ALTER TABLE milestones ADD COLUMN deleted TINYINT NOT NULL DEFAULT 0 COMMENT '软删除'");
        patchColumn("milestones", "version",
            "ALTER TABLE milestones ADD COLUMN version INT NOT NULL DEFAULT 1 COMMENT '乐观锁版本号'");
        patchColumn("milestones", "updated_at",
            "ALTER TABLE milestones ADD COLUMN updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");
        execIgnore("CREATE INDEX idx_milestones_date ON milestones(date)");
        execIgnore("CREATE INDEX idx_milestones_type ON milestones(type)");
        execIgnore("CREATE INDEX idx_milestones_enabled ON milestones(enabled)");
    }

    private void seedGuides() {
        try {
            Integer count = jdbc.queryForObject("SELECT COUNT(*) FROM guides", Integer.class);
            if (count != null && count > 0) {
                return;
            }
        } catch (Exception e) {
            log.warn("DB patch: unable to check guides count: {}", e.getMessage());
            return;
        }

        try {
            jdbc.update("INSERT INTO guides (id, title, type, content, link, order_num, enabled, created_at) VALUES " +
                    "(?, ?, ?, ?, ?, ?, 1, NOW())",
                "guide-arrival-welcome", "到校报到指南", "arrival",
                "按时报到，携带录取通知书、身份证等必需证件，完成注册流程。",
                "https://example.com/arrival", 10);

            jdbc.update("INSERT INTO guides (id, title, type, content, link, order_num, enabled, created_at) VALUES " +
                    "(?, ?, ?, ?, ?, ?, 1, NOW())",
                "guide-dorm-checkin", "宿舍入住流程", "dorm",
                "前往宿舍服务中心领取钥匙并检查宿舍设施，如有问题及时反馈。",
                "https://example.com/dorm", 20);

            jdbc.update("INSERT INTO guides (id, title, type, content, link, order_num, enabled, created_at) VALUES " +
                    "(?, ?, ?, ?, ?, ?, 1, NOW())",
                "guide-international-support", "国际生支持", "international",
                "协助办理签证延期、居留许可，并提供语言与文化适应支持。",
                "https://example.com/international", 30);

            jdbc.update("INSERT INTO guides (id, title, type, content, link, order_num, enabled, created_at) VALUES " +
                    "(?, ?, ?, ?, ?, ?, 1, NOW())",
                "guide-financial-aid", "资助政策速览", "aid",
                "了解奖助学金、勤工助学岗位以及绿色通道办理流程。",
                "https://example.com/aid", 40);
        } catch (Exception e) {
            log.warn("DB patch: failed to seed guides: {}", e.getMessage());
        }
    }

    private void seedFaqs() {
        try {
            Integer count = jdbc.queryForObject("SELECT COUNT(*) FROM faqs", Integer.class);
            if (count != null && count > 0) {
                return;
            }
        } catch (Exception e) {
            log.warn("DB patch: unable to check faqs count: {}", e.getMessage());
            return;
        }

        try {
            jdbc.update("INSERT INTO faqs (id, question, answer, tags, order_num, enabled, created_at) VALUES " +
                    "(?, ?, ?, ?, ?, 1, NOW())",
                "faq-arrival-register",
                "到校报到需要办理哪些手续？",
                "抵校后请携带录取通知书、身份证等证件，前往报到大厅完成身份确认、缴费与寝室分配。",
                "报到,注册",
                10);

            jdbc.update("INSERT INTO faqs (id, question, answer, tags, order_num, enabled, created_at) VALUES " +
                    "(?, ?, ?, ?, ?, 1, NOW())",
                "faq-dorm-essentials",
                "宿舍需要准备哪些生活用品？",
                "建议准备床上用品、个人洗漱用品、电源插排，以及常用药品；校内超市提供补充购买。",
                "宿舍,生活",
                20);

            jdbc.update("INSERT INTO faqs (id, question, answer, tags, order_num, enabled, created_at) VALUES " +
                    "(?, ?, ?, ?, ?, 1, NOW())",
                "faq-international-visa",
                "国际学生入学需要办理哪些签证或居留手续？",
                "请在抵达后10日内前往国际学生中心办理居留许可、签证延期及信息备案，需携护照、照片和录取证明。",
                "国际生,签证,visa",
                30);

            jdbc.update("INSERT INTO faqs (id, question, answer, tags, order_num, enabled, created_at) VALUES " +
                    "(?, ?, ?, ?, ?, 1, NOW())",
                "faq-aid-docs",
                "申请助学金需要准备哪些材料？",
                "需提交家庭经济情况调查表、相关证明材料（如低保证明）、以及个人申请书，按通知时间递交。",
                "资助,助学金,材料",
                40);
        } catch (Exception e) {
            log.warn("DB patch: failed to seed faqs: {}", e.getMessage());
        }
    }

    private void patchColumn(String table, String column, String alterSql) {
        try {
            Integer cnt = jdbc.queryForObject(
                "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_NAME=? AND COLUMN_NAME=?",
                Integer.class, table, column);
            if (cnt != null && cnt > 0) {
                return;
            }
            jdbc.execute(alterSql);
            log.info("DB patch: added column {}.{}", table, column);
        } catch (Exception e) {
            log.warn("DB patch skipped/failed for {}.{}: {}", table, column, e.getMessage());
        }
    }

    private void execIgnore(String sql) {
        try {
            jdbc.execute(sql);
        } catch (Exception ignored) {
        }
    }
}
